package com.opc.quarz;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.opc.Mapper.CarMapper;
import com.opc.Mapper.CarStatusMapper;
import com.opc.Mapper.ScrewMapper;
import com.opc.Mapper.ScrewTypeMapper;
import com.opc.Pojo.*;
import com.opc.Service.CarService;
import com.opc.Service.CarStatusService;
import com.opc.Service.WorkingPositionService;
import com.opc.properties.ScheduledTask;
import com.opc.websocket.WebSocketServer;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.quartz.DisallowConcurrentExecution;
import org.quartz.JobExecutionContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.quartz.QuartzJobBean;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;
@Component
@DisallowConcurrentExecution // 确保任务不会并发执行
@Slf4j
public class DataCleanup  extends QuartzJobBean {
    @Autowired
    private ScheduledTask scheduledTask;
    @Value("${historyData.path}")//必须是org.springframework.beans.factory.annotation提供的Value
    private String basePath;
    @Autowired
    private CarStatusMapper carStatusMapper;
    @Autowired
    private ScrewMapper screwMapper;
    @Autowired
    private CarMapper carMapper;
    @Autowired
    private WebSocketServer webSocketServer;
//    @Qualifier("carStatusService")
    @Autowired
    private CarStatusService carStatusService;
//    @Qualifier("carService")
    @Autowired
    private CarService carService;
//    @Qualifier("workingPositionService")
    @Autowired
    private WorkingPositionService workingPositionService;
    @Autowired
    private ScrewTypeMapper screwTypeMapper;

    private static String convertLocalDateTimeToString(Date Time) {
        LocalDateTime localDateTime = Time.toInstant()
                .atZone(ZoneId.systemDefault())
                .toLocalDateTime();
        String formattedTime = String.format("%04d:%02d:%02d:%02d:%02d:%02d",
                localDateTime.getYear(),
                localDateTime.getMonthValue(),
                localDateTime.getDayOfMonth(),
                localDateTime.getHour(),
                localDateTime.getMinute(),
                localDateTime.getSecond());
//        log.info("formattedTime是"+formattedTime);
        // 使用 replace() 方法替换第一个 ':' 为 '-'
        // 使用正则表达式替换前两个 ':' 为 '-'
        // 拆分字符串
        String[] parts = formattedTime.split(":");
        // 重新组装字符串
        String newFormattedTime = String.join("-", parts[0], parts[1]) + "-" + parts[2] + " " + parts[3] + ":" + parts[4] + ":" + parts[5];
//        log.info("timeFormatter是"+newFormattedTime);
        return newFormattedTime;
    }


    private int exportCarStatusToExcel(List<CarStatus> carStatusList) {
        if (carStatusList == null || carStatusList.isEmpty()) {
            log.info("车告警数据为空！");
            return 0;
        }
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建一个工作表对象
        XSSFSheet sheet = workbook.createSheet("sheet1");
        String[] headers = {"车身号", "车型", "连番号",
                "工位", "螺丝位", "告警值", "单位", "扭力状态", "处理情况"
                , "创建时间", "处理时间"};
        XSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            row1.createCell(i).setCellValue(headers[i]); // Create cell for each header
        }

        for (int i = 0; i < carStatusList.size(); i++) {
            CarStatus carStatus = carStatusList.get(i);
            XSSFRow row2 = sheet.createRow(i + 1);

            // 获取 Car 对象
            Car car = carService.getById(carStatus.getCarId());
            String[] carInfo = {
                    car != null ? String.valueOf(car.getCarId()) : "",
                    car != null ? car.getCarType() : "",
                    car != null ? String.valueOf(car.getRepeatNum()) : "",
            };

            // 获取 Working Position 对象
            Long workingPositionId = carStatus.getWorkingPositionId();
            WorkingPosition workingPosition = workingPositionService.getById(workingPositionId);

            // 获取 Screw Type 对象
            screwType screwType = screwTypeMapper.selectById(carStatus.getScrewTypeId());

            // 获取状态文本
            String statusText;
            switch (carStatus.getAlarmStatus()) {
                case overTorque:
                    statusText = "扭力过大";
                    break;
                case underTorque:
                    statusText = "扭力过小";
                    break;
                case normal:
                    statusText = "正常";
                    break;
//                case twoThirdsAlert:
//                    statusText = "超2/3UCL预警";
//                    break;
                case alertUpperLimit:
                    statusText = "上限预警";
                    break;
                case alertLowerLimit:
                    statusText = "下限预警";
                    break;
                default:
                    statusText = "未知状态"; // 可选，处理其他未定义的状态
                    break;
            }

            // 获取检查状态文本
            String checkStatusText;
            switch (carStatus.getCheckStatus()) {
                case waitForCheck:
                    checkStatusText = "待处理";
                    break;
                case Checked:
                    checkStatusText = "已处理";
                    break;
                case noProcessingRequired:
                    checkStatusText = "无需处理";
                    break;
                default:
                    checkStatusText = "未知情况"; // 可选，处理其他未定义的状态
                    break;
            }

            // 将信息合并到数组中
            String[] carStatusInfo = {
                    carInfo[0], carInfo[1], carInfo[2],
                    workingPosition != null ? String.valueOf(workingPosition.getWorkingPositionName()) : "",
                    screwType != null ? screwType.getType().toString() : "",
                    String.valueOf(carStatus.getTorque()),
                    carStatus.getUnit(),
                    statusText,
                    checkStatusText,
                    convertLocalDateTimeToString(carStatus.getCreateTime()),
                    convertLocalDateTimeToString(carStatus.getUpdateTime())
            };

            // 向 Excel 行中添加数据
            for (int j = 0; j < carStatusInfo.length; j++) {
                row2.createCell(j).setCellValue(carStatusInfo[j]);
            }
        }

        // 使用流获取最早的时间
        Optional<Date> earliestTimeOptional = carStatusList.stream()
                .map(CarStatus::getCreateTime) // 提取创建时间
                .min(Date::compareTo); // 找到最小时间

        // 使用流获取最迟的时间
        Optional<Date> latestTimeOptional = carStatusList.stream()
                .map(CarStatus::getCreateTime) // 提取创建时间
                .max(Date::compareTo); // 找到最大时间
        // 创建日期格式化对象
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
        // 输出结果
        String formattedEarliestTime = null;
        String formattedLatestTime = null;
        if (earliestTimeOptional.isPresent() && latestTimeOptional.isPresent()) {
            formattedEarliestTime = dateFormat.format(earliestTimeOptional.get());
            formattedLatestTime = dateFormat.format(latestTimeOptional.get());
        }

        // 将 Excel 文件保存到 D 盘 DLLS 文件夹
        String Path = basePath + "AlertInfo"+"\\";
        String filePath = Path + "告警报告(" + formattedEarliestTime + "~" + formattedLatestTime + ").xlsx"; // 指定路径，同名就会会覆盖之前生成的文件
        // 确保目录存在，如果不存在则创建
        createDirectoryIfNotExists(Path);
        try (FileOutputStream fileOutputStream = new FileOutputStream(filePath)) {
            workbook.write(fileOutputStream); // 写入数据
            log.info("Excel 文件已成功导出到: " + filePath);
            return 1;
        } catch (IOException e) {
            log.info("导出Excel文件时出错: " + e.getMessage());
            return -1;
        } finally {
            try {
                workbook.close(); // 关闭工作簿
            } catch (IOException e) {
                log.info("关闭工作簿时出错: " + e.getMessage());
            }
        }
    }
    private int exportScrewToExcel(List<Screw> screwList) {
        if (screwList == null || screwList.isEmpty()) {
            log.info("车螺丝数据为空！");
            return 0;
        }
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建一个工作表对象
        XSSFSheet sheet = workbook.createSheet("sheet1");
        String[] headers = {"车身号","车型","连番号","工位", "螺丝位",
                "当前扭力", "单位", "创建时间", "更新时间"};
        XSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            row1.createCell(i).setCellValue(headers[i]); // Create cell for each header
        }

        for (int i = 0; i < screwList.size(); i++) {
            Screw screw = screwList.get(i);
            XSSFRow row2 = sheet.createRow(i + 1);

            // 获取 Car 对象
            Car car = carService.getById(screw.getCarId());
            String[] carInfo = {
                    car != null ? String.valueOf(car.getCarId()) : "",
                    car != null ? car.getCarType() : "",
                    car != null ? String.valueOf(car.getRepeatNum()) : "",
            };

            // 获取 Working Position 对象
            Long workingPositionId = screw.getWorkingPositionId();
            WorkingPosition workingPosition = workingPositionService.getById(workingPositionId);

            // 获取 Screw Type 对象
            screwType screwType = screwTypeMapper.selectById(screw.getScrewTypeId());

            // 将信息合并到数组中
            String[] screwInfo = {
                    carInfo[0], carInfo[1], carInfo[2],
                    workingPosition != null ? String.valueOf(workingPosition.getWorkingPositionName()) : "",
                    screwType != null ? screwType.getType().toString() : "",
                    String.valueOf(screw.getTorque()),
                    screw.getUnit(),
                    convertLocalDateTimeToString(screw.getCreateTime()),
                    convertLocalDateTimeToString(screw.getUpdateTime())
            };

            // 向 Excel 行中添加数据
            for (int j = 0; j < screwInfo.length; j++) {
                row2.createCell(j).setCellValue(screwInfo[j]);
            }
        }

        // 使用流获取最早的时间
        Optional<Date> earliestTimeOptional = screwList.stream()
                .map(Screw::getCreateTime) // 提取创建时间
                .min(Date::compareTo); // 找到最小时间

        // 使用流获取最迟的时间
        Optional<Date> latestTimeOptional = screwList.stream()
                .map(Screw::getCreateTime) // 提取创建时间
                .max(Date::compareTo); // 找到最大时间
        // 创建日期格式化对象
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
        // 输出结果
        String formattedEarliestTime = null;
        String formattedLatestTime = null;
        if (earliestTimeOptional.isPresent() && latestTimeOptional.isPresent()) {
            formattedEarliestTime = dateFormat.format(earliestTimeOptional.get());
            formattedLatestTime = dateFormat.format(latestTimeOptional.get());
        }

        // 将 Excel 文件保存到 D 盘 DLLS 文件夹
        String Path = basePath + "ScrewInfo"+"\\";
        String filePath = Path + "螺丝报告(" + formattedEarliestTime + "~" + formattedLatestTime + ").xlsx"; // 指定路径，同名就会会覆盖之前生成的文件
        // 确保目录存在，如果不存在则创建
        createDirectoryIfNotExists(Path);
        try (FileOutputStream fileOutputStream = new FileOutputStream(filePath)) {
            workbook.write(fileOutputStream); // 写入数据
            log.info("Excel 文件已成功导出到: " + filePath);
            return 1;
        } catch (IOException e) {
            log.info("导出Excel文件时出错: " + e.getMessage());
            return -1;
        } finally {
            try {
                workbook.close(); // 关闭工作簿
            } catch (IOException e) {
                log.info("关闭工作簿时出错: " + e.getMessage());
            }
        }
    }

    private int exportCarToExcel(long threeMonthAgoMillis) {
        LambdaQueryWrapper<Car> carLambdaQueryWrapper = new LambdaQueryWrapper<>();
        carLambdaQueryWrapper.lt(Car::getCreateTime, new Date(threeMonthAgoMillis));
        List<Car> carList = carMapper.selectList(carLambdaQueryWrapper);
        if (carList == null || carList.isEmpty()) {
            log.info("车信息数据为空！");
            return 0;
        }
        // 将 Excel 文件保存到 D 盘 DLLS 文件夹
        String Path = basePath + "CarInfo"+"\\";
        // 确保目录存在，如果不存在则创建
        createDirectoryIfNotExists(Path);

        List<Long> carIds = carList.stream()
                .map(Car::getId)
                .toList();

        LambdaQueryWrapper<Screw> screwLambdaQueryWrapper = new LambdaQueryWrapper<>();
        LambdaQueryWrapper<CarStatus> carStatusLambdaQueryWrapper = new LambdaQueryWrapper<>();
        if (!carIds.isEmpty()) {
            screwLambdaQueryWrapper.in(Screw::getCarId, carIds);
            carStatusLambdaQueryWrapper.in(CarStatus::getCarId, carIds);
            List<Screw> screwList = screwMapper.selectList(screwLambdaQueryWrapper);
            List<CarStatus> carStatusList = carStatusMapper.selectList(carStatusLambdaQueryWrapper);

            log.info("导出screw数据到Excel文件");
            int successStatus1 = exportScrewToExcel(screwList);
            log.info("导出carStatus数据到Excel文件");
            int successStatus2 = exportCarStatusToExcel(carStatusList);
            Map map = new HashMap();
            if(successStatus1 == 0 && successStatus2 == 0){
                map.put("message", "·没有需要备份的螺丝和告警数据·");
                String json = JSON.toJSONString(map);
                webSocketServer.sendToAllClient(json);
            } else if(successStatus1 == 1 && successStatus2 == 1){
                map.put("message", "··螺丝和告警信息备份成功··");
                String json = JSON.toJSONString(map);
                webSocketServer.sendToAllClient(json);
            }else {
                return -1;
            }
        }

        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建一个工作表对象
        XSSFSheet sheet = workbook.createSheet("sheet1");
//        String[] headers = {"车身号","车型","连番号","告警总数", "已解决告警数",
//                "待处理告警数","创建时间", "更新时间"};
        String[] headers = {"车身号","车型","连番号","创建时间", "更新时间"};
        XSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            row1.createCell(i).setCellValue(headers[i]); // Create cell for each header
        }

        for (int i = 0; i < carList.size(); i++) {
            Car car = carList.get(i);
            XSSFRow row2 = sheet.createRow(i + 1);

            String[] carInfo = {
                    car != null ? String.valueOf(car.getCarId()) : "",
                    car != null ? car.getCarType() : "",
                    car != null ? String.valueOf(car.getRepeatNum()) : "",
            };

            // 将信息合并到数组中
            String[] CarInfo = null;
            if (car != null) {
                CarInfo = new String[]{
                        carInfo[0], carInfo[1], carInfo[2],
    //                    String.valueOf(car.getAlarmCount()),
    //                    String.valueOf(car.getSolvedAlarmCount()),
    //                    String.valueOf(car.getPendingAlarmCount()),
                        convertLocalDateTimeToString(car.getCreateTime()),
                        convertLocalDateTimeToString(car.getUpdateTime())
                };
            }

            // 向 Excel 行中添加数据
            if (CarInfo != null) {
                for (int j = 0; j < CarInfo.length; j++) {
                    row2.createCell(j).setCellValue(CarInfo[j]);
                }
            }
        }

        // 使用流获取最早的时间
        Optional<Date> earliestTimeOptional = carList.stream()
                .map(Car::getCreateTime) // 提取创建时间
                .min(Date::compareTo); // 找到最小时间

        // 使用流获取最迟的时间
        Optional<Date> latestTimeOptional = carList.stream()
                .map(Car::getCreateTime) // 提取创建时间
                .max(Date::compareTo); // 找到最大时间

        // 创建日期格式化对象
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd_HH-mm-ss");
        // 输出结果
        String formattedEarliestTime = null;
        String formattedLatestTime = null;
        if (earliestTimeOptional.isPresent() && latestTimeOptional.isPresent()) {
            formattedEarliestTime = dateFormat.format(earliestTimeOptional.get());
            formattedLatestTime = dateFormat.format(latestTimeOptional.get());
        }
        String filePath = Path + "车信息报告(" + formattedEarliestTime + "~" + formattedLatestTime + ").xlsx"; // 指定路径，同名就会会覆盖之前生成的文件

        try (FileOutputStream fileOutputStream = new FileOutputStream(filePath)) {
            workbook.write(fileOutputStream); // 写入数据
            log.info("Excel 文件已成功导出到: " + filePath);
            return 1;
        } catch (IOException e) {
            log.info("导出Excel文件时出错: " + e.getMessage());
            return -1;
        } finally {
            try {
                workbook.close(); // 关闭工作簿
            } catch (IOException e) {
                log.info("关闭工作簿时出错: " + e.getMessage());
            }
        }
    }

    private void createDirectoryIfNotExists(String directoryPath) {
        Path path = Paths.get(directoryPath);
        // 检查目录是否存在
        if (!Files.exists(path)) {
            try {
                // 创建目录（包括必要的父目录）
                Files.createDirectories(path);
                log.info("目录已创建: " + directoryPath);
            } catch (IOException e) {
                System.err.println("无法创建目录: " + e.getMessage());
                if(e.getMessage().contains("Unable to determine if root directory exists")){
                    log.info("创建文件夹失败，没有这个盘");
                    Map map = new HashMap<>();
                    int colonIndex = basePath.indexOf(':');
                    if (colonIndex != -1) {
                        String result = basePath.substring(0, colonIndex);
                        map.put("message", "创建备份目录失败，找不到磁盘"+result);
                    }
                    String json = JSON.toJSONString(map);
                    webSocketServer.sendToAllClient(json);
                }
            }
        } else {
            log.info("目录已存在: " + directoryPath);
        }
    }

    @Override
    @Transactional
    @Async
    public void executeInternal(JobExecutionContext context) {
//        LocalDateTime threeYearsAgo = LocalDateTime.now().minusYears(4);
        //清楚三个月前的数据
        LocalDateTime threeMonthAgo = LocalDateTime.now().minusMonths(Long.parseLong(scheduledTask.getDataCleaned()));//.minusHours(6);//3//10
        //System.out.println("当前时间："+LocalDateTime.now());
        long threeMonthAgoMillis = threeMonthAgo.atZone(ZoneId.systemDefault()).toInstant().toEpochMilli();
        log.info("清除数据库时间："+threeMonthAgoMillis);
        //System.out.println("清除数据库时间："+threeMonthAgoMillis);
        log.info("导出三个月前的数据成excel表");

        int successStatus = exportCarToExcel(threeMonthAgoMillis);

        if(successStatus == 0){
            Map map = new HashMap<>();
            map.put("message", "没有需要备份的数据");
            String json = JSON.toJSONString(map);
            webSocketServer.sendToAllClient(json);
            return;
        }else if(successStatus == -1){
            Map map = new HashMap<>();
            int colonIndex = basePath.indexOf(':');
            if (colonIndex != -1) {
                String result = basePath.substring(0, colonIndex);
                map.put("message", "车信息备份失败，找不到磁盘"+result);
            }
            String json = JSON.toJSONString(map);
            webSocketServer.sendToAllClient(json);
            return;
        }else {
            Map map = new HashMap<>();
            map.put("message", "车信息已备份到"+basePath);
            String json = JSON.toJSONString(map);
            webSocketServer.sendToAllClient(json);
        }

        log.info("3定时清除数据库三个月前的车信息数据");
        LambdaQueryWrapper<Car> carQueryWrapper = new LambdaQueryWrapper<>();
        carQueryWrapper.lt(Car::getCreateTime, new Date(threeMonthAgoMillis));
//        carQueryWrapper.le(Car::getCreateTime,System.currentTimeMillis()-3*365*24*60*60*1000);
        List<Car> cars = carMapper.selectList(carQueryWrapper);

        List<Long> carIds = cars.stream().map(Car::getId).toList();
        // 收集待删除的告警 IDs
        LambdaQueryWrapper<CarStatus> lambdaQueryWrapper1 = new LambdaQueryWrapper<>();
        if (!carIds.isEmpty()) {
            lambdaQueryWrapper1.in(CarStatus::getCarId, carIds);
            carStatusMapper.delete(lambdaQueryWrapper1);
        }
        // 收集待删除的螺丝 IDs
        LambdaQueryWrapper<Screw> lambdaQueryWrapper2 = new LambdaQueryWrapper<>();
        if (!carIds.isEmpty()) {
            lambdaQueryWrapper2.in(Screw::getCarId, carIds);
            screwMapper.delete(lambdaQueryWrapper2);
        }
        carMapper.delete(carQueryWrapper);
        log.info("3清除车机数据完成");
        log.info("----清除数据库完成----");
    }
}
